package com.delicious.dao.impl;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.delicious.dao.BaseDao;
import com.delicious.dao.NewsDao;
import com.delicious.entity.News;

public class NewsDaoImpl extends BaseDao implements NewsDao {

	public List<News> getNewsPaging(int pageNum, int pageSize) {
		List<News> list = new ArrayList<News>();
		String sql = "SELECT a.*,(@rowNum:=@rowNum+1) AS rowNo"
					+" FROM (SELECT * FROM news ORDER BY PublishDate DESC ";
		openConnection();
		ResultSet rs = null;
		ArrayList keyword = new ArrayList();
		
		if(pageNum>0 && pageSize>0){
			sql += "LIMIT ?,?";
			keyword.add((pageNum-1)*pageSize);
			keyword.add(pageSize);
		}
		sql += ") a, (SELECT (@rowNum :=0) ) b";
		
		try {
			rs = executeQuery(sql, keyword.toArray());
			while(rs.next()){
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String publishDate = rs.getString("publishDate");
				int userid = rs.getInt("userid");
				News n = new News(id, title, content, publishDate, userid);
				list.add(n);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection();
		}
		
		return list;
	}

	public News getNewsByRowNo(int rowNo) {
		News n = null;
		String sqlA = "select n.* from ";
		String sqlB = "(SELECT a.*,(@rowNum:=@rowNum+1) AS rowNo FROM (SELECT * FROM news ORDER BY PublishDate DESC) a,(SELECT (@rowNum :=0) ) b) n ";
		String sql = sqlA+sqlB;
		sql+="limit ?,?";
		ResultSet rs = null;
		openConnection();
		
		try {
			rs = executeQuery(sql, new Object[]{(rowNo-1),rowNo});
			if(rs.next()){
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String publishDate = rs.getString("publishDate");
				int userid = rs.getInt("userid");
				n = new News(id, title, content, publishDate, userid);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return n;
	}

	public static void main(String[] args) {
		News n = new NewsDaoImpl().getNewsByRowNo(3);
		System.out.println(n.getTitle());
	}
}
